
import time
import pymysql
import smtplib
import subprocess
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

con = pymysql.connect(host="localhost",user="inaripiadmin79417",password="kali92010linux",database="inari_pi", unix_socket="/run/mysqld/mysqld.sock")
cursor = con.cursor()
select_client_email= ("SELECT email FROM tbl_user WHERE id_user = 2")
cursor.execute(select_client_email)
con.commit()
rows= cursor.fetchall()
for row in rows:
    client_email= row[0]
    con.close()


while True:
    sender_email = "inaripi@outlook.com"
    receiver_email = client_email
    password = *OMITTED*

    leasesfile = open("/var/lib/misc/dnsmasq.leases", "r")

 
    for line in leasesfile:
       values = line.split(" ") 
       mac= values[1] 
       ip= values[2]
       name = values[3] 
       con = pymysql.connect(host="localhost",user="inaripiadmin79417",password="kali92010linux",database="inari_pi", unix_socket="/run/mysqld/mysqld.sock") 
       cursor = con.cursor() 
       retrieve= ("SELECT * FROM tbl_device WHERE mac_address = %s") 
       mac_val = mac.upper()
       con = pymysql.connect(host="localhost",user="inaripiadmin79417",password="kali92010linux",database="inari_pi", unix_socket="/run/mysqld/mysqld.sock")
       cursor = con.cursor()
       cursor.execute(retrieve, mac_val) 
       rows= cursor.fetchall() 
       if not rows : 
           sql= ("INSERT INTO tbl_device (device_name, mac_address,ip_address, online) VALUES (%s, %s, %s, %s)")
           val =(name, mac_val,ip, 'y')
           cursor.execute(sql, val)
           print(f"New mac address detected: {mac_val}.")
           

           
           message = MIMEMultipart("alternative")
           message["Subject"] = "Activity Notification"
           message["From"] = 'inaripi@outlook.com'
           message["To"] = 'inaripi_client@hotmail.com'


           html = """\
           <!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <style media="screen">s
    body{
      font-family: sans-serif;}
    .footer{
      text-align: center;
      font-size: 10px;
      color: #999999;}
    .page{
      background-color: #ffffff;}
.message{
  padding: 10px 20px;
  margin: 10px;
  background-color:#ffffff;}
 a {
  background-color: #ffffff;
  border: solid 1px #3498dc;
  border-radius: 4px;
  box-sizing: border-box;
  color: #3498dc;
  font-size: 14px;
  font-weight: bold;
  padding: 12px 25px;
  text-decoration: none;
  text-align:center;}
hr{
  background-color: gray;
  border-width: thin}

  .page{
  padding: 30px;
  background-color: #f6f6f6;
}


  </style>

  <title>Notification</title>
</head>
<body>
<div class="page">
<div class="message">
  <p>Dear IPI user,</p><br>
  <p>The following  device:<b> """ + name + """</b> has connected to the network.<br> Please login to manage it.</p>
    <p>If you are not connected to your home network, access remotely.</p><br>
  <a href="https://192.168.4.1/index.php">Login To The portal</a> <a href="https://inaripiservice.eu.ngrok.io">Login Remotely</a></p><br>


  <p>Many thanks,</p>
  <p><strong>The Inari PI Team.<strong></p>
<hr>
    <div class="footer">
    <span>Inari PI @ 2021, all rights reserved.</span>
    </div>
    </div>
</div>
</body>
</html>

        """

           part2 = MIMEText(html, "html")
           message.attach(part2)

           with smtplib.SMTP("smtp-mail.outlook.com", 587) as server:
               server.starttls()
               server.login(sender_email, password)
               server.sendmail(
               sender_email, receiver_email, message.as_string()
               )
       else:
         
            print(f"Mac address already in database: {mac_val}")
            ipneigh=subprocess.run("ip neigh | grep "+ip+" | cut -d ' ' -f 6",  shell=True, check=True, text=True, capture_output=True)
            response= ipneigh.stdout
            #print(response)

            if "REACHABLE" in response:
                online= ("UPDATE tbl_device SET online = 'y' WHERE ip_address = %s")
                cursor.execute(online, ip)
                print(f"{ip} is connected and active.")
            elif "STALE" in response:
                online= ("UPDATE tbl_device SET online = 'y' WHERE ip_address = %s")
                cursor.execute(online, ip)
                print(f"{ip} is connected but inactive.")
            else:
                print(f"{ip} is not connected.")
                not_online= ("UPDATE tbl_device SET online = 'n' WHERE ip_address = %s")
                cursor.execute(not_online, ip)
       
       con.commit()
       con.close()
    time.sleep(15)
   


leasesfile.close()
